﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace PClass
{
    public class DatabaseHandler
    {
        private String dbName;
        private String Password;

        private SQLiteConnection sqliteConnection;
        private SQLiteConnectionStringBuilder sqliteConStrBu;
        private SQLiteCommand sqliteCmd;
        private SQLiteException sqliteException;
        private String querySql;

        private bool isDBOpen = false;

        public DatabaseHandler(String dbname, String password)
        {
            this.dbName = dbname;
            this.Password = password;
            if (!File.Exists(dbname))
            {
                SQLiteConnection.CreateFile(dbName);
            }
            sqliteConnection = new SQLiteConnection();
            sqliteConStrBu = new SQLiteConnectionStringBuilder();
            sqliteCmd = new SQLiteCommand();

        }

        //打开数据库
        public void openDB()
        {
            if (!isDBOpen)
            {
                sqliteConStrBu.DataSource = dbName;
                sqliteConStrBu.Password = Password;//设置密码，SQLite ADO.NET实现了数据库密码保护

                sqliteConnection.ConnectionString = sqliteConStrBu.ToString();

                sqliteConnection.Open();
                isDBOpen = true;
            }
        }
        //关闭数据库
        public void closeDB()
        {
            if (isDBOpen)
            {
                sqliteConnection.Close();
                isDBOpen = false;
            }
        }

        //创建表
        public void createTable(String tableName, String[] volumeName, ValueType[] valuesType)
        {
            int volumeLength = volumeName.Length;
            if (volumeLength < 1 || volumeLength != valuesType.Length)
            {
                return;
            }
            else
            {
                querySql = "create table if not exists " + tableName + "(";
                int i = 0;
                for (; i < volumeLength - 1; i++)
                {
                    querySql += volumeName[i] + " " + valuesType[i] + ",";
                }
                querySql += volumeName[i] + " " + valuesType[i] + ")";
                try
                {
                    openDB();
                    sqliteCmd.CommandText = querySql;
                    sqliteCmd.Connection = sqliteConnection;
                    sqliteCmd.ExecuteNonQuery();
                    closeDB();
                }
                catch
                {
                }
            }
        }

        //创建表
        public void createTable()
        {
            querySql = "create table if not exists printparam(Id varchar,Value varchar)";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

        //添加值
        public void insertValue(int id,String value)
        {
            querySql = "insert into printparam values(@Id,@Value)";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Parameters.AddWithValue("@Id", id);
                sqliteCmd.Parameters.AddWithValue("@Value", value);
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                closeDB();
            }
            catch (SQLiteException e)
            {
                sqliteException = e;
                throw sqliteException;
                //Console.WriteLine(e.ToString());
            }
        }
        //删除所有信息
        public void deleteAllValue()
        {
            querySql = "delete from printparam";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }
        //查询记录
        public String findValue(int id)
        {
            DataTable dt = new DataTable();
            querySql = "select * from printparam where Id=" + id;
            String value = "";
            SQLiteDataReader reader = null;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                reader = sqliteCmd.ExecuteReader();
                if (reader.Read())
                {
                    value += reader.GetString(1)+" ";
                }
                reader.Close();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                reader.Close();
            }
            return value;
        }
        //查看某记录是否存在
        public bool isValueExist(int Id)
        {
            int count = -1;
            querySql = "select count(*) from printparam where Id =" + Id;
            SQLiteDataReader reader = null;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                reader = sqliteCmd.ExecuteReader();
                if (reader.Read()) count = reader.GetInt16(0);
                else count = 0;
                reader.Close();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                reader.Close();
                return false;
            }
            if (count > 0)
                return true;
            else return false;
        }
        //获取总记录数
        public int getCount()
        {
            int count = 0;
            querySql = "select count(*) count from printparam";
            SQLiteDataReader reader = null;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                reader = sqliteCmd.ExecuteReader();
                if (reader.Read())
                    count = reader.GetInt16(0);
                else count = 0;
                reader.Close();
                closeDB();
                return count;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                reader.Close();
                return 0;
            }
        }
        //修改记录值
        public void updateValue(int id, String value)
        {
            if (getCount()<1)
            {
                return;
            }
            querySql = "update printparam set Id=" + id + ",Value='" + value + "' where Id="+id;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }
    }
}
